* Table 8
use "temp/EIA_clean.dta", clear

preserve
	keep if importer==1
	bys year: egen sip10 = pctile(si*100), p(10)
	bys year: egen sip25 = pctile(si*100), p(25)
	bys year: egen sip50 = pctile(si*100), p(50)
	bys year: egen sip75 = pctile(si*100), p(75)
	bys year: egen sip95 = pctile(si*100), p(95)
	bys year: egen siavg = mean(si*100)
	collapse sip10 sip25 sip50 sip75 sip95 siavg, by(year)
	label var sip10 "10th pctile si"
	label var sip25 "25th pctile si"
	label var sip50 "50th pctile si"
	label var sip75 "75th pctile si"
	label var sip95 "95th pctile si"
	label var siavg "siavg"
	keep if year>=1994 & year<=1999
	* compute change between 1994 and 95-99 average
	gen temp1 = 0
	replace temp1=1 if year>1994
	foreach x of var sip* siavg {
	bys temp1: egen `x'avg = mean(`x')
	drop `x'
	}	
	drop temp1
	keep if year==1994 | year==1995
	foreach x of var sip*avg siavgavg {
	gen `x'_d = `x' - `x'[_n-1]
	drop `x'
	}	
	keep if year==1995
	drop year	
	label var sip10avg_d "10th pctile si"
	label var sip25avg_d "25th pctile si"
	label var sip50avg_d "50th pctile si"
	label var sip75avg_d "75th pctile si"
	label var sip95avg_d "95th pctile si"
	label var siavgavg_d "Mean"
	export excel "../../Output/Table8_Data", first(varl) sh("First") replace
restore
bys year: egen totalmatfor = total(matfor) 
bys year: gen siAGG = totalmatfor / totalmat // aggregate imported input share
preserve
	collapse siAGG, by (year)
	label var siAGG "Import Share"
	* compute change between avg of 95-99 and 94
	keep if year>=1994 & year<=1999
	gen temp1 = 0
	replace temp1 = 1 if year==1994
	foreach x of var siAGG{
	gen `x'temp = `x' * temp1
	egen `x'94 = total(`x'temp)
	gen `x'_delta = (`x'-`x'94)*100
	drop `x'temp
	}
	drop if year==1994
	collapse siAGG_delta
	label var siAGG_delta "Aggregate"
	export excel "../../Output/Table8_Data", first(varl) sh("Second") 
restore
gen lva = log(mi)
bys year importer: egen CORRlnvasiIM = corr(lva si)
preserve
	keep if importer==1
	collapse CORRlnvasiIM, by(year)
	label var CORRlnvasiIM "Corr va si (Importers)"
	keep if year>=1994 & year<=1999
	gen temp1 = 0
	replace temp1 = 1 if year==1994
	foreach x of var CORRlnvasiIM {
	gen `x'temp = `x' * temp1
	egen `x'94 = total(`x'temp)
	gen `x'_delta = (`x'-`x'94)
	drop `x'temp
	}
	drop if year==1994
	collapse CORRlnvasiIM_delta
	label var CORRlnvasiIM_delta "Corr. w/ sales"
	export excel  using "../../Output/Table8_Data", first(varl) sh("Third") 
restore





